package com.MutiModule.common.utils;

import java.io.BufferedInputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.HashMap;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

/**
 * 操作Excel表格的功能类
 */
public class ExcelReader {

    /**
     * 读取Excel表格表头的内容
     * @param InputStream
     * @return String 表头内容的数组
     */
    public static String[] readExcelTitle(String filePath) {
        try {
        	FileInputStream input = new FileInputStream(new File(filePath));
        	if(filePath.endsWith(".xls")) {
        		HSSFWorkbook wb = new HSSFWorkbook(new BufferedInputStream(input));
        		HSSFSheet sheet = wb.getSheetAt(0);
        		HSSFRow row = sheet.getRow(0);
        		// 标题总列数
        		int colNum = row.getPhysicalNumberOfCells();
        		String[] title = new String[colNum];
        		for (int i = 0; i < colNum; i++) {
        			title[i] = getHCellFormatValue(row.getCell((short) i));
        		}
        		return title;
        	} else if(filePath.endsWith(".xlsx")) {
        		XSSFWorkbook wb = new XSSFWorkbook(new BufferedInputStream(input));
        		XSSFSheet sheet = wb.getSheetAt(0);
        		XSSFRow row = sheet.getRow(0);
        		// 标题总列数
        		int colNum = row.getPhysicalNumberOfCells();
        		String[] title = new String[colNum];
        		for (int i = 0; i < colNum; i++) {
        			title[i] = getXCellFormatValue(row.getCell((short) i));
        		}
        		return title;
        	}
        } catch (IOException e) {
            e.printStackTrace();
        }
        return null;
    }

    /**
     * 读取Excel数据内容
     * @param InputStream
     * @return Map 包含单元格数据内容的Map对象
     */
    public static Map<Integer, StringBuffer> readExcelContent(String filePath) {
        Map<Integer, StringBuffer> content = new HashMap<Integer, StringBuffer>();
        if(filePath.endsWith(".xls")) {
        	StringBuffer str = new StringBuffer();
        	try {
        		FileInputStream input = new FileInputStream(new File(filePath));
        		HSSFWorkbook wb = new HSSFWorkbook(new BufferedInputStream(input));
        		HSSFSheet sheet = wb.getSheetAt(0);
        		int rowNum = sheet.getLastRowNum();
        		HSSFRow row = sheet.getRow(0);
        		int colNum = row.getPhysicalNumberOfCells();
        		for (int i = 1; i <= rowNum; i++) {
        			row = sheet.getRow(i);
        			int j = 0;
        			while (j < colNum) {
        				str.append(getHCellFormatValue(row.getCell((short) j)).trim() + "    ");
        				j++;
        			}
        			content.put(i, str);
        			str = new StringBuffer();
        		}
        	} catch (IOException e) {
        		e.printStackTrace();
        	}
        } else if(filePath.endsWith(".xlsx")) {
        	StringBuffer str = new StringBuffer();
        	try {
        		FileInputStream input = new FileInputStream(new File(filePath));
        		XSSFWorkbook wb = new XSSFWorkbook(new BufferedInputStream(input));
        		XSSFSheet sheet = wb.getSheetAt(0);
        		int rowNum = sheet.getLastRowNum();
        		XSSFRow row = sheet.getRow(0);
        		int colNum = row.getPhysicalNumberOfCells();
        		for (int i = 1; i <= rowNum; i++) {
        			row = sheet.getRow(i);
        			int j = 0;
        			while (j < colNum) {
        				str.append(getXCellFormatValue(row.getCell((short) j)).trim() + "    ");
        				j++;
        			}
        			content.put(i, str);
        			str = new StringBuffer();
        		}
        	} catch (IOException e) {
        		e.printStackTrace();
        	}
        }
        return content;
    }
    
    /**
     * 根据文件路径，sheetNum，获取sheet
     * @param filePath	文件路径
     * @param sheetNum	sheetNum编号
     * @return
     */
    public static Sheet readExcelSheet(String filePath, int sheetNum) {
    	Sheet sheet = null;
    	if(filePath.endsWith(".xls")) {
        	try {
        		FileInputStream input = new FileInputStream(new File(filePath));
        		HSSFWorkbook wb = new HSSFWorkbook(new BufferedInputStream(input));
        		sheet = wb.getSheetAt(0);
        	} catch (IOException e) {
        		e.printStackTrace();
        	}
        } else if(filePath.endsWith(".xlsx")) {
        	try {
        		FileInputStream input = new FileInputStream(new File(filePath));
        		XSSFWorkbook wb = new XSSFWorkbook(new BufferedInputStream(input));
        		sheet = wb.getSheetAt(0);
        	} catch (IOException e) {
        		e.printStackTrace();
        	}
        }
    	return sheet;
    }
    
    /**
     * 根据sheet对象，获取到rowNum行 cellNum列 的数值
     * @param sheet	sheet对象
     * @param rowNum	行编码
     * @param cellNum	列编码
     * @return
     */
    public static String readExcelCellValue(Sheet sheet, int rowNum, int cellNum) {
    	Row row = sheet.getRow(rowNum);
    	String cellValue = getCellFormatValue(row.getCell((short) cellNum)).trim();
    	return cellValue;
    }

    /**
     * 获取单元格数据内容为字符串类型的数据
     * 
     * @param cell Excel单元格
     * @return String 单元格数据内容
     */
    public static String getStringCellValue(HSSFCell cell) {
        String strCell = "";
        switch (cell.getCellType()) {
        case HSSFCell.CELL_TYPE_STRING:
            strCell = cell.getStringCellValue();
            break;
        case HSSFCell.CELL_TYPE_NUMERIC:
            strCell = String.valueOf(cell.getNumericCellValue());
            break;
        case HSSFCell.CELL_TYPE_BOOLEAN:
            strCell = String.valueOf(cell.getBooleanCellValue());
            break;
        case HSSFCell.CELL_TYPE_BLANK:
            strCell = "";
            break;
        default:
            strCell = "";
            break;
        }
        if (strCell.equals("") || strCell == null) {
            return "";
        }
        if (cell == null) {
            return "";
        }
        return strCell;
    }

    /**
     * 获取单元格数据内容为日期类型的数据
     * 
     * @param cell
     *            Excel单元格
     * @return String 单元格数据内容
     */
    public static String getDateCellValue(HSSFCell cell) {
        String result = "";
        try {
            int cellType = cell.getCellType();
            if (cellType == HSSFCell.CELL_TYPE_NUMERIC) {
                Date date = cell.getDateCellValue();
                result = (date.getYear() + 1900) + "-" + (date.getMonth() + 1)
                        + "-" + date.getDate();
            } else if (cellType == HSSFCell.CELL_TYPE_STRING) {
                String date = getStringCellValue(cell);
                result = date.replaceAll("[年月]", "-").replace("日", "").trim();
            } else if (cellType == HSSFCell.CELL_TYPE_BLANK) {
                result = "";
            }
        } catch (Exception e) {
            System.out.println("日期格式不正确!");
            e.printStackTrace();
        }
        return result;
    }

    /**
     * 根据HSSFCell类型设置数据
     * @param cell
     * @return
     */
    public static String getHCellFormatValue(HSSFCell cell) {
        String cellvalue = "";
        if (cell != null) {
            // 判断当前Cell的Type
            switch (cell.getCellType()) {
            // 如果当前Cell的Type为NUMERIC
            case HSSFCell.CELL_TYPE_NUMERIC:
            case HSSFCell.CELL_TYPE_FORMULA: {
                // 判断当前的cell是否为Date
                if (HSSFDateUtil.isCellDateFormatted(cell)) {
                    // 如果是Date类型则，转化为Data格式
                    
                    //方法1：这样子的data格式是带时分秒的：2011-10-12 0:00:00
                    //cellvalue = cell.getDateCellValue().toLocaleString();
                    
                    //方法2：这样子的data格式是不带带时分秒的：2011-10-12
                    Date date = cell.getDateCellValue();
                    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                    cellvalue = sdf.format(date);
                    
                }
                // 如果是纯数字
                else {
                    // 取得当前Cell的数值
                    cellvalue = String.valueOf(cell.getNumericCellValue());
                }
                break;
            }
            // 如果当前Cell的Type为STRIN
            case HSSFCell.CELL_TYPE_STRING:
                // 取得当前的Cell字符串
                cellvalue = cell.getRichStringCellValue().getString();
                break;
            // 默认的Cell值
            default:
                cellvalue = " ";
            }
        } else {
            cellvalue = "";
        }
        return cellvalue;

    }
    
    /**
     * 根据XSSFCell类型设置数据
     * @param cell
     * @return
     */
    public static String getXCellFormatValue(XSSFCell cell) {
        String cellvalue = "";
        if (cell != null) {
            // 判断当前Cell的Type
            switch (cell.getCellType()) {
            // 如果当前Cell的Type为NUMERIC
            case HSSFCell.CELL_TYPE_NUMERIC:
            case HSSFCell.CELL_TYPE_FORMULA: {
                // 判断当前的cell是否为Date
                if (HSSFDateUtil.isCellDateFormatted(cell)) {
                    // 如果是Date类型则，转化为Data格式
                    
                    //方法1：这样子的data格式是带时分秒的：2011-10-12 0:00:00
                    //cellvalue = cell.getDateCellValue().toLocaleString();
                    
                    //方法2：这样子的data格式是不带带时分秒的：2011-10-12
                    Date date = cell.getDateCellValue();
                    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                    cellvalue = sdf.format(date);
                    
                }
                // 如果是纯数字
                else {
                    // 取得当前Cell的数值
                    cellvalue = String.valueOf(cell.getNumericCellValue());
                }
                break;
            }
            // 如果当前Cell的Type为STRIN
            case HSSFCell.CELL_TYPE_STRING:
                // 取得当前的Cell字符串
                cellvalue = cell.getRichStringCellValue().getString();
                break;
            // 默认的Cell值
            default:
                cellvalue = " ";
            }
        } else {
            cellvalue = "";
        }
        return cellvalue;

    }
    
    public static String getCellFormatValue(Cell cell) {
        String cellvalue = "";
        if (cell != null) {
            // 判断当前Cell的Type
            switch (cell.getCellType()) {
            // 如果当前Cell的Type为NUMERIC
            case HSSFCell.CELL_TYPE_NUMERIC:
            case HSSFCell.CELL_TYPE_FORMULA: {
                // 判断当前的cell是否为Date
                if (HSSFDateUtil.isCellDateFormatted(cell)) {
                    // 如果是Date类型则，转化为Data格式
                    
                    //方法1：这样子的data格式是带时分秒的：2011-10-12 0:00:00
                    //cellvalue = cell.getDateCellValue().toLocaleString();
                    
                    //方法2：这样子的data格式是不带带时分秒的：2011-10-12
                    Date date = cell.getDateCellValue();
                    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                    cellvalue = sdf.format(date);
                    
                }
                // 如果是纯数字
                else {
                    // 取得当前Cell的数值
                    cellvalue = String.valueOf(cell.getNumericCellValue());
                }
                break;
            }
            // 如果当前Cell的Type为STRIN
            case HSSFCell.CELL_TYPE_STRING:
                // 取得当前的Cell字符串
                cellvalue = cell.getRichStringCellValue().getString();
                break;
            // 默认的Cell值
            default:
                cellvalue = " ";
            }
        } else {
            cellvalue = "";
        }
        return cellvalue;

    }

}